/* BORRADO DE FUNCIONES, VISTAS y TABLAS */

DROP VIEW IF EXISTS empleados_con_accesos_fallidos;

DROP TABLE IF EXISTS auditoria CASCADE;
DROP TABLE IF EXISTS contrato CASCADE;
DROP TABLE IF EXISTS empleado_profesional_contratado CASCADE;
DROP TABLE IF EXISTS empleado_profesional_planta_permanente CASCADE ;
DROP TABLE IF EXISTS empleado_profesional CASCADE;
DROP TABLE IF EXISTS empleado_jerarquico CASCADE;
DROP TABLE IF EXISTS empleado_no_profesional_acceso CASCADE;
DROP TABLE IF EXISTS empleado_no_profesional CASCADE;
DROP TABLE IF EXISTS acceso CASCADE;
DROP TABLE IF EXISTS empleado CASCADE;
DROP TABLE IF EXISTS evento CASCADE;
DROP TABLE IF EXISTS area CASCADE;
DROP TABLE IF EXISTS nivel_seguridad CASCADE;

DROP FUNCTION IF EXISTS checkempleadonoprofesional();
DROP FUNCTION IF EXISTS checkempleadojerarquico();
DROP FUNCTION IF EXISTS checkempleadoprofesional();
DROP FUNCTION IF EXISTS checkprofesionalcontratado();
DROP FUNCTION IF EXISTS checkprofesionalplantapermanente();
DROP FUNCTION IF EXISTS checkaccesoposiblevalido();

/* CREACION DE TABLAS */

/* nivel de seguridad */
CREATE TABLE nivel_seguridad (
  nivel_seguridad_id INTEGER,
  descripcion TEXT,
  PRIMARY KEY (nivel_seguridad_id)
);

/* area */
CREATE TABLE area (
  area_id INTEGER PRIMARY KEY,
  nivel_seguridad_id INTEGER REFERENCES nivel_seguridad,
  nombre TEXT
);

/* evento */
CREATE TABLE evento (
  area_id INTEGER REFERENCES area,
  evento_id INTEGER,
  fecha_hora TIMESTAMP,
  descripcion TEXT,
  PRIMARY KEY (area_id, evento_id)
);

/* empleado */
CREATE TABLE empleado (
  empleado_id INTEGER,
  nombre TEXT,
  apellido TEXT,
  categoria INTEGER,
  PRIMARY KEY (empleado_id)
);

/* empleado no profesional*/
CREATE TABLE empleado_no_profesional (
  empleado_id INTEGER PRIMARY KEY REFERENCES empleado,
  nivel_seguridad_id INTEGER REFERENCES nivel_seguridad
);

/* empleado no profesional acceso */
CREATE TABLE empleado_no_profesional_acceso (
  empleado_id INTEGER REFERENCES empleado,
  area_id INTEGER REFERENCES area,
  franja_horaria_inicio time,
  franja_horaria_fin time,
  PRIMARY KEY (empleado_id, area_id)
);

/* empleado jerarquico */
CREATE TABLE empleado_jerarquico (
  empleado_id INTEGER REFERENCES empleado,
  area_id INTEGER REFERENCES area,
  fecha_inicio_responsable date,
  PRIMARY KEY (empleado_id, area_id)
);

/* empleado profesional */
CREATE TABLE empleado_profesional (
  empleado_id INTEGER PRIMARY KEY REFERENCES empleado,
  tipo_profesional INTEGER
);

/* empleado profesional planta permanente */
CREATE TABLE empleado_profesional_planta_permanente (
  empleado_id INTEGER PRIMARY KEY REFERENCES empleado,
  area_id INTEGER REFERENCES area
);

/* empleado profesional contratado */
CREATE TABLE empleado_profesional_contratado (
  empleado_id INTEGER PRIMARY KEY REFERENCES empleado
);

/* contrato */
CREATE TABLE contrato (
  empleado_id INTEGER REFERENCES empleado_profesional_contratado,
  numero_contrato INTEGER,
  area_id INTEGER REFERENCES area,
  fecha_inicio date,
  fecha_fin date,
  tarea TEXT,
  PRIMARY KEY (empleado_id, numero_contrato)
);

/* auditoria */
CREATE TABLE auditoria (
  empleado_id INTEGER,
  numero_contrato INTEGER,
  auditoria_id INTEGER,
  fecha_hora TIMESTAMP,
  resultado TEXT,
  PRIMARY KEY (empleado_id, numero_contrato, auditoria_id),
  FOREIGN KEY (empleado_id, numero_contrato) REFERENCES contrato (empleado_id, numero_contrato)
);

/* acceso */
CREATE TABLE acceso (
  empleado_id INTEGER REFERENCES empleado,
  area_id INTEGER REFERENCES area,
  acceso_id INTEGER,
  metodo_autenticacion TEXT,
  fue_autorizado INTEGER,
  fecha_hora_ingreso TIMESTAMP,
  fecha_hora_egreso TIMESTAMP,
  PRIMARY KEY (empleado_id, area_id, acceso_id)
);

/* CONSTRAINTS, TRIGGERS, VIEWS y STORES PROCEDURES */

/* 
Punto 4 de los "Comentarios de la cátedra"
verifica que un empleado_no_profesional no pueda ser asignado a un area que no tiene el nivel de seguridad 
 */
CREATE FUNCTION CheckAccesoPosibleValido() RETURNS trigger AS '
BEGIN
  IF (SELECT 
        nivel_seguridad_id 
      FROM 
        area
      WHERE
        area_id = NEW.area_id) >
     (SELECT 
        nivel_seguridad_id
      FROM
        empleado_no_profesional
      WHERE
        empleado_id = NEW.empleado_id) THEN
    RAISE EXCEPTION ''Este empleado no profesional no tiene el nivel de seguridad para poder acceder a esta area.'';
  END IF;
  RETURN NEW;
END' LANGUAGE plpgsql;

CREATE TRIGGER AccesoPosibleValido
  BEFORE INSERT OR UPDATE ON empleado_no_profesional_acceso
  FOR EACH ROW
  EXECUTE PROCEDURE CheckAccesoPosibleValido();


/* 
Punto 5 de los "Comentarios de la cátedra"
Se eligió poner contraints en las tablas que representan una "jerarquia" que verifiquen que un insert en una tabla 
"hijo" coincida con el valor del discriminante en la tabla padre. 
En el modelo hay dos jerarquías

empleado: la columna discrimimante es "categoria" y puede tener los valor 0, 1 o 2
categoria = 0: El hijo va en la tabla "empleado_no_profesional"
categoria = 1: El hijo va en la tabla "empleado_jerarquico"
categoria = 2: El hijo va en la tabla "empleado_profesional"

empleado_profesional: la columna discrimimante es "tipo_profesional" y puede tener los valor 0 o 1
tipo_profesional = 0: El hijo va en la tabla "empleado_profesional_planta_permanente"
tipo_profesional = 1: El hijo va en la tabla "empleado_profesional_contratado"

*/

/* CONSTRAINT: empleado.categoria puede valer 0, 1 o 2 */
ALTER TABLE empleado ADD constraint CategoriaValida check(categoria >= 0 AND categoria <= 2);

/* CONSTRAINT: emploado_profesional.tipo_profesional puede valer 0 o 1 */
ALTER TABLE empleado_profesional ADD constraint TipoProfesionalValido check(tipo_profesional >= 0 AND tipo_profesional <= 1);

/* FUNCTION+TRIGGER: verifica que un empleado no profesional tenga el valor 0 en la empleado.categoria */
CREATE FUNCTION CheckEmpleadoNoProfesional() RETURNS trigger AS '
BEGIN
  IF (SELECT 
          categoria 
      FROM 
          empleado 
      WHERE 
          empleado_id = NEW.empleado_id
      ) != 0 THEN
    RAISE EXCEPTION ''Solo se puede insertar empleados no profesionales en empleado_no_profesional.'';
  END IF;
  RETURN NEW;    
END' LANGUAGE plpgsql;
    
CREATE TRIGGER EmpleadoNoProfesional
  BEFORE INSERT OR UPDATE ON empleado_no_profesional
  FOR EACH ROW
  EXECUTE PROCEDURE CheckEmpleadoNoProfesional();

/* FUNCTION+TRIGGER: verifica que un empleado jerarquico tenga el valor 1 en la empleado.categoria */
CREATE FUNCTION CheckEmpleadoJerarquico() RETURNS trigger AS '
BEGIN
  IF (SELECT
        categoria
      FROM
        empleado
      WHERE
        empleado_id = NEW.empleado_id
      ) != 1 THEN
    RAISE EXCEPTION ''Solo se puede agregar personal jerarquico a empleado_jerarquico.'';
  END IF;
  RETURN NEW;
END' LANGUAGE plpgsql;

CREATE TRIGGER EmpleadoJerarquico
  BEFORE INSERT OR UPDATE ON empleado_jerarquico
  FOR EACH ROW
  EXECUTE PROCEDURE CheckEmpleadoJerarquico();

/* FUNCTION+TRIGGER: verifica que un empleado profesional tenga el valor 2 en la empleado.categoria */
CREATE FUNCTION CheckEmpleadoProfesional() RETURNS trigger AS '
BEGIN
  IF (SELECT
        categoria
      FROM
        empleado
      WHERE
        empleado_id = NEW.empleado_id
      ) != 2 THEN
    RAISE EXCEPTION ''Solo se puede agregar personal profesional a empleado_profesional.'';
  END IF;
  RETURN NEW;
END' LANGUAGE plpgsql;

CREATE TRIGGER EmpleadoProfesional
  BEFORE INSERT OR UPDATE ON empleado_profesional
  FOR EACH ROW
  EXECUTE PROCEDURE CheckEmpleadoProfesional();

/* FUNCTION+TRIGGER: verifica que un empleado profesional contratado tenga el valor 1 en la empleado_profesional.tipo_profesional */
CREATE FUNCTION CheckProfesionalContratado() RETURNS trigger AS '
BEGIN
  IF (SELECT
        tipo_profesional
      FROM
        empleado_profesional
      WHERE
        empleado_id = NEW.empleado_id) != 1 THEN
    RAISE EXCEPTION ''Solo los profesionales contratados pueden ser agregados a empleado_profesional_contratado.'';
  END IF;
  RETURN NEW;
END' LANGUAGE plpgsql;

CREATE TRIGGER ProfesionalContratado
  BEFORE INSERT OR UPDATE ON empleado_profesional_contratado
  FOR EACH ROW
  EXECUTE PROCEDURE CheckProfesionalContratado();


/* FUNCTION+TRIGGER: verifica que un empleado profesional planta permantente tenga el valor 0 en la empleado_profesional.tipo_profesional */
CREATE FUNCTION CheckProfesionalPlantaPermanente() RETURNS trigger AS '
BEGIN
  IF (SELECT
        tipo_profesional
      FROM
        empleado_profesional
      WHERE
        empleado_id = NEW.empleado_id) != 0 THEN
    RAISE EXCEPTION ''Solo los profesionales de planta permanente pueden ser agregados a empleado_profesional_planta_permanente.'';
  END IF;
  RETURN NEW;
END' LANGUAGE plpgsql;

CREATE TRIGGER ProfesionalPlantaPermanente
  BEFORE INSERT OR UPDATE ON empleado_profesional_planta_permanente
  FOR EACH ROW
  EXECUTE PROCEDURE CheckProfesionalPlantaPermanente();


/* vista de empleados con accesos fallidos. Consigna 2 de "Comentarios de la cátedra" */

CREATE VIEW empleados_con_accesos_fallidos AS
SELECT
  emp.empleado_id empleado_id,
  emp.nombre empleado_nombre,
  emp.apellido empleado_apellido,
  area.area_id area_id,
  area.nombre area_nombre
FROM
  empleado emp,
  area area
WHERE
  EXISTS (
    SELECT 1 FROM acceso acc1 WHERE
    acc1.empleado_id = emp.empleado_id
    AND acc1.area_id = area.area_id
    AND acc1.fue_autorizado = 0
    AND CURRENT_DATE = date(acc1.fecha_hora_ingreso)
    AND NOT EXISTS (
      SELECT 1 FROM acceso acc2 WHERE
      acc2.empleado_id = emp.empleado_id
      AND acc2.area_id = area.area_id
      AND acc2.fue_autorizado = 1
      AND acc1.fecha_hora_ingreso < acc2.fecha_hora_ingreso
      AND CURRENT_DATE = date(acc2.fecha_hora_ingreso)
    )
  )
;

